The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.
Normalization is a design technique that is used to structure relational database tables. As used herein, “normal form” refers to a structural organization that indicates the relationships among the columns of a database table or tables. For example, the most commonly used normal forms are first, second, and third normal forms. A relational table would be in first Normal Form (1NF) when the table is configured to store atomic values in all of its columns and a set of columns is designated as a primary key, where the values of the primary key uniquely identify the values in the rest of the columns across all rows in the table. A relational table that is in first normal form would be converted to second Normal Form (2NF) by removing to separate table or tables the columns that are configured for storing subsets of data that is associated with multiple rows of the table, and then creating foreign key columns in the separate table or tables for linking back to the table. A relational table that is in second normal form would be converted to third Normal Form (3NF) by removing columns from the table that do not depend on the entire set of primary key columns. Normalization of relational database tables is typically used to reduce data duplication and to avoid various types of logical inconsistencies that may cause loss of data integrity when data is inserted, updated, and deleted from a table.
If conventional normalization techniques are applied and a relational table is created in third normal form, all data rows stored in the table would be identifiable by the values stored in the primary key columns. However, the data rows stored in the relational table would not depend in any way on security policies that may need to be applied to certain columns of the table. For example, some columns of a relational table in third normal form may store sensitive data access to which may need to be restricted (e.g. salary data, credit card numbers, etc.), while other columns of the table may store non-sensitive data access to which need not be restricted (e.g. name, telephone number, etc.) However, the conventional normalization techniques do not provide any mechanisms or rules that would allow different columns with different security policies to be stored in the same relational table, even though the table may be properly designed in third normal form and values in each of the different columns may be uniquely identifiable by nothing else but the values in the primary key columns.
For example, consider an employee directory that is designed as a relational table organized in third normal form. A particular employee may be represented as a row in the relational table, where the various attributes of the employee (e.g., name, position, phone number, Social Security Number, date of birth, salary, etc.) are stored as separate columns in the table. In this example, some of the attribute data for the particular employee is sensitive (e.g., Social Security Number, date of birth, salary), and accordingly must be made available only to authorized users such as the manager of the employee. On the other hand, access to the non-sensitive attribute data for the employee (e.g., name, position, and phone number) should be made available without restriction to facilitate easier communication among employees. However, if the employee directory table is created and stored in third normal form, then the sensitive and non-sensitive employee data would be stored as columns in the same table. In turn, this would create a security problem because sensitive and non-sensitive employee data needs to be accessed according to different security rules and policies.
To address this security problem, after the employee directory table is designed as a single relational table in third normal form, one past approach provides for separating the columns storing sensitive and non-sensitive employee data and then creating the separated columns in two or more different tables. Then, existing database security mechanisms are used to assign different permissions to the two or more different tables, and these permissions are subsequently used to control access to these tables. One serious disadvantage of this approach is that it violates the third normal form principle and thus increases data redundancy. Another serious disadvantage of this approach is that referential integrity needs to be additionally enforced between the different tables that store employee data in order to provide for data integrity and prevent logical inconsistencies in the database (for example, rows representing a particular employee must be deleted from all tables that store employee data when an employee leaves the company.)
Another approach to address the above security problem is to entrust front-end applications and/or clients with enforcing the security policies that control access to sensitive and non-sensitive employee data. For example, the employee directory table may be designed and created as a single relational table in third normal form to include columns storing both sensitive and non-sensitive employee data. Thereafter, it would be up to the front-end applications to check and determine whether a particular user that issues a request has the permissions to access the sensitive employee data. One serious disadvantage of this approach is that different front-end applications may not implement in a uniform and consistent way the security policies that control access to the sensitive and non-sensitive employee data. For example, some front-end applications may consider date-of-birth data as non-sensitive data, while other front-end applications may consider the same data as sensitive. Another serious disadvantage of this approach is that any user (e.g. a database administrator or an intruder) who gains direct access to the employee directory table through a database client API or a query tool would be able to retrieve sensitive employee data as long as that user has sufficient permissions to access the table.
Based on the foregoing, techniques for implementing secure normal forms are needed that overcome the disadvantages of the approaches described above.